
ALTER                 proc sp_products_list_with_base_order_qty
@customer_id  int
as
begin
	declare @results Table(ProcessStep_ID int, IST_Manufacturing_Part_Number varchar (50), IST_Marketing_Part_Number varchar (50), Product_ID int, P_Type varchar (50), P_Comments varchar (200),Base_Order_Qty varchar(200))
	declare @proc_id varchar (20)
	declare @mft_part_no varchar (50)
	declare @mkt_part_no varchar (50)
	declare @prod_id int
	declare @p_type varchar (50)
	declare @p_description varchar (200)
	declare @base_order_qty int

	declare @process_step_id int
	declare @units_per_tray int
	declare @trays_per_innerbox int
	declare @innerboxes_per_outerbox int		

	-- Added by Sarada on 6th jan 2004
	insert into @results values(null,'---- CHIPS -----','---- CHIPS -----',null,null,null,null)
	
	declare ncursor cursor for
	select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type, Product.P_Comments from product,Process_Step  where product.P_Type is null and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.P_Marketing_part_Number

	--SELECT inv.ProcessStep_ID ProcessStep_ID,IST_Manufacturing_Part_Number,IST_Marketing_Part_Number,product.Product_ID, P_Type FROM Inventory_Step_Table inv ,Product,Process_Step  where Process_Step.Ps_Type = 'Finished Goods' and inv.ProcessStep_ID = product.ProcessStep_ID and inv.ProcessStep_ID = Process_Step.ProcessStep_ID and p_type IS NULL 
	open ncursor
		fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description 


		while @@fetch_status = 0
		begin
			-- Prabhanjan (29 Apr 2004)
			SELECT @process_step_id = ProcessStep_ID from PRODUCT WHERE PRODUCT_ID = @prod_id
			SELECT @units_per_tray = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Units Per Tray' and  processstep_id = @process_step_id
			SELECT @trays_per_innerbox = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Trays Per InnerBox' and  processstep_id = @process_step_id
			SELECT @innerboxes_per_outerbox = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'InnerBoxes Per OuterBox' and  processstep_id = @process_step_id

			IF (@units_per_tray IS NULL)
			BEGIN
				SET @units_per_tray = 0
			END	

			IF (@trays_per_innerbox IS NULL)
			BEGIN
				SET @trays_per_innerbox = 0
			END	
			
			IF (@innerboxes_per_outerbox IS NULL)
			BEGIN
				SET @innerboxes_per_outerbox = 0
			END	

			--SET @base_order_qty = @units_per_tray * @trays_per_innerbox * @innerboxes_per_outerbox
			SET @base_order_qty = @units_per_tray * @trays_per_innerbox

			IF (@base_order_qty IS NULL)
			BEGIN
				SET @base_order_qty = 0
			END	

			-- Prabhanjan (29 Apr 2004)

			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@base_order_qty)

			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			
			set @units_per_tray = null
			set @trays_per_innerbox = null
			set @base_order_qty = 0
			fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description 

		end
	close ncursor
	deallocate ncursor
	insert into @results values(null,'---- BOARD -----','---- BOARD -----',null,null,null,null)
	-- Added another condition (and p_parent_product_id <> -1) as child part numbers are introduced in Boards ravikanth

	declare bcursor cursor for
	--select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'BOARD' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.P_Marketing_part_Number
	--SELECT inv.ProcessStep_ID ProcessStep_ID,IST_Manufacturing_Part_Number,IST_Marketing_Part_Number,product.Product_ID, P_Type FROM Inventory_Step_Table inv ,Product,Process_Step  where Process_Step.Ps_Type = 'Finished Goods' and inv.ProcessStep_ID = product.ProcessStep_ID and inv.ProcessStep_ID = Process_Step.ProcessStep_ID and p_type = 'BOARD' 
	-- Modified by Prabhanjan (15 Apr 2004)
	select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'BOARD' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID and Product.P_Parent_Product_ID = -1 order by Product.P_Marketing_part_Number
		
	open bcursor
		
		fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description


		while @@fetch_status = 0
		begin
			-- Added by Prabhanjan (15 Apr 2004)
			declare childCursor cursor for
			select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step where product.P_Type = 'BOARD' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID and Product.P_Parent_Product_ID = @prod_id order by Product.P_Marketing_part_Number

			
			open childCursor			
			fetch next from childCursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description

			-- Prabhanjan (29 Apr 2004)
			SELECT @process_step_id = ProcessStep_ID from PRODUCT WHERE PRODUCT_ID = @prod_id
			SELECT @units_per_tray = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Units Per Tray' and  processstep_id = @process_step_id
			SELECT @trays_per_innerbox = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Trays Per InnerBox' and  processstep_id = @process_step_id
			SELECT @innerboxes_per_outerbox = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'InnerBoxes Per OuterBox' and  processstep_id = @process_step_id

			IF (@units_per_tray IS NULL)
			BEGIN
				SET @units_per_tray = 0
			END	

			IF (@trays_per_innerbox IS NULL)
			BEGIN
				SET @trays_per_innerbox = 0
			END	
			
			IF (@innerboxes_per_outerbox IS NULL)
			BEGIN
				SET @innerboxes_per_outerbox = 0
			END	

			--SET @base_order_qty = @units_per_tray * @trays_per_innerbox * @innerboxes_per_outerbox
			SET @base_order_qty = @units_per_tray * @trays_per_innerbox

			IF (@base_order_qty IS NULL)
			BEGIN
				SET @base_order_qty = 0
			END	
			-- Prabhanjan (29 Apr 2004)

			if @@fetch_status <> 0
			begin
				insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@base_order_qty)
			end

			while @@fetch_status = 0
			begin
				insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@base_order_qty)
				set @proc_id = null
				set @mft_part_no = null
				set @mkt_part_no = null
				set @prod_id = null
				set @p_type = null
				set @p_description = null
				set @units_per_tray = null
				set @trays_per_innerbox = null

				set @base_order_qty = 0
	
				fetch next from childCursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description
			end
			close childCursor
			deallocate childCursor

			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @units_per_tray = null
			set @trays_per_innerbox = null
			set @base_order_qty = 0

		fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
		
		end
	close bcursor
	deallocate bcursor
	insert into @results values(null,'---- KIT -----','---- KIT -----',null,null,null,null)
	-- Added another condition (and p_parent_product_id <> -1) as child part numbers are introduced in Kits ravikanth
	declare kcursor cursor for
	select Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type,Product.P_Comments from product,Process_Step  where product.P_Type = 'KIT' and product.Is_Approved = 0 and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID order by Product.P_Marketing_part_Number
 --SELECT inv.ProcessStep_ID ProcessStep_ID,IST_Manufacturing_Part_Number,IST_Marketing_Part_Number,product.Product_ID, P_Type FROM Inventory_Step_Table inv ,Product,Process_Step  where Process_Step.Ps_Type = 'Finished Goods' and inv.ProcessStep_ID = product.ProcessStep_ID and inv.ProcessStep_ID = Process_Step.ProcessStep_ID and p_type = 'KIT' 
	open kcursor
		fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 

		while @@fetch_status = 0
		begin
			-- Prabhanjan (29 Apr 2004)
			SELECT @process_step_id = ProcessStep_ID from PRODUCT WHERE PRODUCT_ID = @prod_id
			SELECT @units_per_tray = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Units Per Tray' and  processstep_id = @process_step_id
			SELECT @trays_per_innerbox = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Trays Per InnerBox' and  processstep_id = @process_step_id
			SELECT @innerboxes_per_outerbox = Parameter_Value from PROCESS_PARAMETER WHERE PARAMETER_KEY = 'InnerBoxes Per OuterBox' and  processstep_id = @process_step_id

			IF (@units_per_tray IS NULL)
			BEGIN
				SET @units_per_tray = 0
			END	

			IF (@trays_per_innerbox IS NULL)
			BEGIN
				SET @trays_per_innerbox = 0
			END	
			
			IF (@innerboxes_per_outerbox IS NULL)
			BEGIN
				SET @innerboxes_per_outerbox = 0
			END	

			--SET @base_order_qty = @units_per_tray * @trays_per_innerbox * @innerboxes_per_outerbox
			SET @base_order_qty = @units_per_tray * @trays_per_innerbox

			IF (@base_order_qty IS NULL)
			BEGIN
				SET @base_order_qty = 0
			END	

			-- Prabhanjan (29 Apr 2004)			

			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@base_order_qty )
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @units_per_tray = null
			set @trays_per_innerbox = null
			set @base_order_qty = 0
			fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
			
		end
	close kcursor
	deallocate kcursor
end
select * from @results   
